package com.mixmer.excelanalysis;

import com.mixmer.excelanalysis.entity.DirectoryEnum;
import com.mixmer.excelanalysis.excel.CellLocation;
import com.mixmer.excelanalysis.excel.ExcelAnalysisUtil;
import com.mixmer.excelanalysis.expression.ExpressionEvaluator;
import com.mixmer.excelanalysis.properties.PropertiesUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.*;

public class ExcelAnalysisApplication {

    public static void main(String[] args) throws IOException {
        String basePath = System.getProperty("user.dir").replaceAll("\\\\", "/");
        // todo
//        basePath = "";
        System.out.println("welcome~");
        System.out.println("请先完成以下配置：");
        System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
        System.out.println("1. 【必选】将待处理的excel文件放入 " + (basePath + DirectoryEnum.SRC_DIRECTORY.getDirectoryName()) + " 文件夹中;");
        System.out.println("2. 【可选】如果需要使用具体公式处理表格，请完成 " + basePath + DirectoryEnum.CONF_DIRECTORY.getDirectoryName() + "formula.properties 中公式配置。");
        System.out.println("++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
        System.out.println("完成以上配置后请按任意键继续。");
        Scanner scanner = new Scanner(System.in);
        String s = scanner.nextLine();
        System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
        System.out.println("您要处理的文件列表为:");
        // 解析conf.properties,拿到待处理文件列表
        String confPreopertiesPath = basePath + DirectoryEnum.CONF_DIRECTORY.getDirectoryName()
                + "/conf.properties";
        // todo
//        confPreopertiesPath = "D:/conf/conf.properties";
        PropertiesUtil confProUtil = new PropertiesUtil(confPreopertiesPath);
        String srcFilePath = confProUtil.getProperty("srcFile");
        File file = new File(basePath + srcFilePath);
        ArrayList<File> fileList = new ArrayList<>();
        if (file.isDirectory()) {
            File[] files = file.listFiles();
            for (File srcFile : files) {
                if (srcFile.getName().endsWith("xlsx")) {
                    fileList.add(srcFile);
                }
            }
        } else {
            if (file.getName().endsWith("xlsx")) {
                fileList.add(file);
            }
        }
        for (File srcFile : fileList) {
            System.out.println(srcFile.getName());
        }
        System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
        System.out.println("请输入您要选择的解析模式：");
        System.out.println("1. 具体公式;");
        System.out.println("2. 批量模糊处理;");
        // 解析公式配置文件,拿到所有公式表达式
        String formulaConfFile = basePath + DirectoryEnum.CONF_DIRECTORY.getDirectoryName() + "/formula.properties";
        // todo
//        formulaConfFile = "D:/conf/formula.properties";
        PropertiesUtil formulaProUtil = new PropertiesUtil(formulaConfFile);
        String dealMode = scanner.nextLine();
        boolean dealModeFlag = true;
        while (dealModeFlag) {
            if ("1".equals(dealMode)) {
                dealModeFlag = false;
            } else if ("2".equals(dealMode)) {
                // 清空公式
                formulaProUtil.clearAllProperty();
                System.out.println("请输入您要处理的sheet页范围：");
                System.out.println("eg: 9=1-7(第9个sheet页的内容为第1到第7sheet页的和)");
                String sheetRange = scanner.nextLine();
                String[] split = sheetRange.split("=");
                int targetSheetIndex = Integer.parseInt(split[0]) - 1;
                String[] sheetRangeArr = split[1].split("-");
                int startSheetIndex = Integer.parseInt(sheetRangeArr[0]) - 1;
                int endSheetIndex = Integer.parseInt(sheetRangeArr[1]) - 1;
                ArrayList<Integer> sheetNumList = new ArrayList<>();
                for (int i = startSheetIndex; i <= endSheetIndex; i++) {
                    sheetNumList.add(i);
                }
                Set<String> cellLocationSet = new HashSet<>();
                for (File srcFile : fileList) {
                    Workbook workbook = ExcelAnalysisUtil.getWorkbook(srcFile.getCanonicalPath());
                    // 遍历startSheet到endSheet，找到有数字格式的单元格，记录
                    int colNum = 100;
                    int rowNum = workbook.getSheetAt(startSheetIndex).getLastRowNum();
                    for (int i = 0; i <= rowNum; i++) {
                        for (int j = 0; j < colNum; j++) {
                            for (int sheetIndex : sheetNumList) {
                                CellLocation cellLocation = new CellLocation(sheetIndex, i, j);
                                CellType cellType = ExcelAnalysisUtil.getCellType(cellLocation);
                                // 如果这个单元格是数字的话，记录
                                if (Objects.nonNull(cellType) && CellType.NUMERIC.equals(cellType)) {
                                    String cellLocationStr = (i + 1) + "-" + (char) (j + 65);
                                    cellLocationSet.add(cellLocationStr);
                                    break;
                                }
                            }
                        }
                    }
                }
                for (String cellLocation : cellLocationSet) {
                    ArrayList<String> list = new ArrayList<>();
                    for (Integer integer : sheetNumList) {
                        list.add("[" + (integer + 1) + "-" + cellLocation + "]");
                    }
                    formulaProUtil.writeProperties((targetSheetIndex + 1) + "-" + cellLocation, StringUtils.join(list, "+"));
                }
                dealModeFlag = false;
            } else {
                dealMode = scanner.nextLine();
                System.out.println("解析模式选择错误，请重新选择!");
            }
        }
        System.out.println("将要应用的公式为:");
        Map<String, String> formulaMap = formulaProUtil.getAllProperty();
        formulaMap.forEach((k, v) -> System.out.println(k + "=" + v));
        System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
        System.out.println("请确认以上信息是否正确？继续请按任意键");
        s = scanner.nextLine();
        for (File srcFile : fileList) {
            dealExcelDataByFormula(formulaMap, srcFile);
        }
    }

    private static void dealExcelDataByFormula(Map<String, String> formulaMap, File file) {
        try {
            System.out.println("----------------------------------------------------------------------------------------");
            System.out.println(file.getName() + " 正在处理中..");
            ExcelAnalysisUtil.getWorkbook(file.getCanonicalPath());
            formulaMap.forEach((k, v) -> {
                //(1-2-C)+(2-3-A)
                String[] split = v.split("\\[");
                for (String str : split) {
                    // str: 2-5-B
                    if (StringUtils.isNotBlank(str)) {
                        String[] strArr = str.split("]");
                        String target = strArr[0];
                        CellLocation cellLocation = CellLocation.getCellLocation(target);
                        // 从excel文件中获取对应单元格的值
                        String cellValue = ExcelAnalysisUtil.getCellValue(cellLocation.getSheetIndex(),
                                cellLocation.getRowIndex(), cellLocation.getColIndex());
                        v = v.replace(target, cellValue);
                    }
                }
                v = v.replace("[", "");
                v = v.replace("]", "");
                Object eval = ExpressionEvaluator.eval(v);
                assert eval != null;
                System.out.print(k + " = " + v + " = ");
                try {
                    v = new DecimalFormat("#,###.00").format(Double.valueOf(eval.toString()));
                } catch (NumberFormatException e) {
                    v = eval.toString();
                }
                System.out.println(v);
                CellLocation cellLocation = CellLocation.getCellLocation(k);
                ExcelAnalysisUtil.setCellValue(cellLocation.getSheetIndex(), cellLocation.getRowIndex(), cellLocation.getColIndex(), v);
            });
            String basePath = System.getProperty("user.dir").replaceAll("\\\\", "/");
            String targetFilePath = basePath + DirectoryEnum.TARGET_DIRECTORY.getDirectoryName() + file.getName();
            // todo
//            targetFilePath = "D:/targetFile/" + file.getName();
            ExcelAnalysisUtil.writeExcelFile(targetFilePath);
            System.out.println("----------------------------------------------------------------------------------------");
            System.out.println(targetFilePath + " 处理完成!");
            System.out.println();
            System.out.println();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}
